/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package CONTROLLERS;

import BEANS.ComboItem;
import BEANS.Product;
import UTIL.DatabaseConnector;
import UTIL.InputValidator;
import UTIL.TableManager;
import datechooser.beans.DateChooserCombo;
import java.awt.Point;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JCheckBox;
import javax.swing.JDialog;
import javax.swing.JTable;
import VIEW.ReportsSales;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import javax.swing.JLabel;
import javax.swing.table.TableModel;
import CONTROLLERS.ProductController;
import VIEW.Reports;
import java.awt.Color;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import javax.swing.JComboBox;
import javax.swing.JFileChooser;
import javax.swing.JTextField;
/**
 *
 * @author AMD
 */
public class ReportsController {
    private ProductController productController;
    private DatabaseConnector dbConnector = DatabaseConnector.getInstance();
    private TableManager transactionTableManager;
    private TableManager inventoryTableManager;
    private JCheckBox salesCheckBox;
    private JCheckBox deliveryCheckBox;
    private JCheckBox returnsCheckBox;
    private JCheckBox transferCheckBox;
    private JCheckBox tradeInCheckBox;
    private JCheckBox adjustmentsCheckBox;
    private JCheckBox replacementCheckBox;
    private JDialog salesDialog;
    private JLabel salesProd;
    private JLabel salesDate;
    private JLabel salesReceipt;
    private JLabel salesQty;
    private JLabel salesSerial;
    private JLabel salesType;
    private JLabel salesRemarks;
    private JDialog deliveryDialog;
    private JLabel deliveryDate;
    private JLabel deliverySupp;
    private JLabel deliveryReceipt;
    private JLabel deliveryQty;
    private JLabel deliveryProd;
    private JLabel deliveryRemarks;
    private JDialog transferDialog;
    private JLabel transferProd;
    private JLabel transferQty;
    private JLabel transferFrom;
    private JLabel transferTo;
    private JLabel transferDate;
    private JLabel transferRemarks;
    private JDialog returnsDialog;
    private JLabel returnsProd;
    private JLabel returnsSupplier;
    private JLabel returnsDate;
    private JLabel returnsQty;
    private JLabel returnsRemarks;
    private JLabel returnsStatus;
    private JLabel returnsSerial;
    private JDialog adjustmentsDialog;
    private JLabel adjProd;
    private JLabel adjOperation;
    private JLabel adjQty;
    private JLabel adjDate;
    private JLabel adjReason;
    private JDialog tradeInDialog;
    private JLabel tradeProd;
    private JLabel tradeTo;
    private JLabel tradeQty;
    private JLabel tradeType;
    private JLabel tradeDate;
    //private JLabel tradeRemarks;
    private JLabel rplProd;
    private JLabel rplWith;
    private JLabel rplQty;
    private JLabel rplRcpt;
    private JLabel rplDate;
    private JLabel rplRemarks;
    private JTextField searchTextField; 
    private JLabel clearMark;
    private JComboBox<ComboItem> productFilterCategoryComboBox;
    private JComboBox<ComboItem> productFilterSupplierComboBox;
    private static ReportsSales reportsSales;
    private DateChooserCombo dateTo;
    private DateChooserCombo dateFrom;  
    private JTable inventoryTable;
    private JTable transactionTable;
    private JCheckBox brandNew;
    private JCheckBox secondHand;
    private JCheckBox defective;
    private JDialog replacementDialog;
    
    public ReportsController(JTable transactionTable, JTable inventoryTable, JCheckBox salesCheckBox, JCheckBox deliveryCheckBox,
        JCheckBox returnsCheckBox, JCheckBox transferCheckBox, JCheckBox tradeInCheckBox, JCheckBox adjustmentsCheckBox, JCheckBox replacementCheckBox,
        JDialog salesDialog, JLabel salesProd, JLabel salesDate, JLabel salesReceipt, JLabel salesQty, JLabel salesSerial, JLabel salesType,
        JDialog deliveryDialog, JLabel deliveryDate, JLabel deliverySupp, JLabel deliveryReceipt, JLabel deliveryQty, JLabel deliveryProd,
        JDialog transferDialog, JLabel transferProd, JLabel transferQty, JLabel transferFrom, JLabel transferTo, JLabel transferDate,
        JDialog returnsDialog, JLabel returnsProd, JLabel returnsSupplier, JLabel returnsDate, JLabel returnsQty, JLabel returnsRemarks, JLabel returnsStatus, JLabel returnsSerial,
        JDialog adjustmentsDialog, JLabel adjProd, JLabel adjOperation, JLabel adjQty, JLabel adjDate, JLabel adjReason, 
        DateChooserCombo dateFrom, DateChooserCombo dateTo, JDialog tradeInDialog, JLabel tradeProd, JLabel tradeTo, JLabel tradeQty,
        JLabel tradeType, JLabel tradeDate, JTextField searchTextField, JComboBox<ComboItem> productFilterCategoryComboBox, 
        JComboBox<ComboItem> productFilterSupplierComboBox, JLabel clearMark, JCheckBox brandNew, JCheckBox secondHand, JCheckBox defective,
        JLabel salesRemarks, JLabel deliveryRemarks, JLabel transferRemarks, JLabel rplProd, JLabel rplWith, JLabel rplQty,
        JLabel rplRcpt, JLabel rplDate, JLabel rplRemarks, JDialog replacementDialog){
        productController = new ProductController();
        transactionTableManager = new TableManager(transactionTable);
        inventoryTableManager = new TableManager(inventoryTable);
        this.replacementDialog = replacementDialog;
        this.salesRemarks = salesRemarks;
        this.deliveryRemarks = deliveryRemarks;
        this.transferRemarks = transferRemarks;
        //this.tradeRemarks = tradeRemarks;
        this.rplProd = rplProd;
        this.rplWith = rplWith;
        this.rplQty = rplQty;
        this.rplRcpt = rplRcpt;
        this.rplDate = rplDate;
        this.rplRemarks = rplRemarks;
        this.brandNew = brandNew;
        this.secondHand = secondHand;
        this.defective = defective;
        this.inventoryTable = inventoryTable;
        this.transactionTable = transactionTable;
        this.tradeInDialog = tradeInDialog;
        this.tradeProd = tradeProd;
        this.tradeTo = tradeTo;
        this.tradeQty = tradeQty;
        this.tradeType = tradeType;
        this.tradeDate = tradeDate;
        this.adjustmentsDialog = adjustmentsDialog;
        this.adjProd = adjProd;
        this.adjOperation = adjOperation;
        this.adjQty = adjQty;
        this.adjDate = adjDate;
        this.adjReason = adjReason;
        this.deliveryDialog = deliveryDialog;
        this.deliveryDate = deliveryDate;
        this.deliverySupp = deliverySupp;
        this.deliveryReceipt = deliveryReceipt;
        this.deliveryQty = deliveryQty;
        this.deliveryProd = deliveryProd;
        this.transferDialog = transferDialog;
        this.transferProd = transferProd;
        this.transferQty = transferQty;
        this.transferFrom = transferFrom;
        this.transferTo = transferTo;
        this.transferDate = transferDate;
        this.returnsDialog = returnsDialog;
        this.returnsProd = returnsProd;
        this.returnsSupplier = returnsSupplier;
        this.returnsDate = returnsDate;
        this.returnsQty = returnsQty;
        this.returnsRemarks = returnsRemarks;
        this.returnsStatus = returnsStatus;
        this.returnsSerial = returnsSerial;
        this.salesCheckBox = salesCheckBox;
        this.deliveryCheckBox = deliveryCheckBox;
        this.returnsCheckBox = returnsCheckBox;
        this.transferCheckBox= transferCheckBox;
        this.tradeInCheckBox = tradeInCheckBox;
        this.adjustmentsCheckBox = adjustmentsCheckBox;
        this.replacementCheckBox = replacementCheckBox;
        this.salesDialog = salesDialog;
        this.salesProd = salesProd;
        this.salesDate = salesDate;
        this.salesReceipt = salesReceipt;
        this.salesQty = salesQty;
        this.salesSerial = salesSerial;
        this.salesType = salesType;
        
        this.clearMark = clearMark;
        this.productFilterCategoryComboBox = productFilterCategoryComboBox;
        this.productFilterSupplierComboBox = productFilterSupplierComboBox;
        transactionTableManager.hideColumn(7);
        transactionTableManager.hideColumn(6);
        inventoryTableManager.hideColumn(4);
        //transactionTableManager.hideColumn(5);
        this.dateTo = dateTo;
        this.dateFrom = dateFrom;
        this.searchTextField = searchTextField;
        
    }
    
    public void updateCurrentInventory(){
        ArrayList<Integer> defectiveRows = new ArrayList<Integer>();
        ArrayList<Integer> secondhandRows = new ArrayList<Integer>();
        inventoryTableManager.clearTableContents();
        if(brandNew.isSelected()){
            int row = 0;
            int rows = 0;
            updateBrandNewReportsTable();
            for(int i=0; i< inventoryTable.getRowCount(); i++) {    
                String type = inventoryTableManager.getValueAt(i, 4);
                row++;
                if(type.equals("defective")){
                    defectiveRows.add(row);
                }else if(type.equals("secondhand")){
                    secondhandRows.add(row);
                }
            }
            inventoryTableManager.setRowStatusColor(defectiveRows, Color.BLUE);            
            inventoryTableManager.setRowStatusColor(defectiveRows, Color.GREEN);
        }
        
        if(secondHand.isSelected()){
            updateSecondHandReportsTable();
        }
        
        if(defective.isSelected()){
            updateDefectiveReportsTable();
        }
    }
    
    public void updateReportTable(){
        transactionTableManager.clearTableContents();
        if(salesCheckBox.isSelected()){
            salesReport();
        }
        
        if(deliveryCheckBox.isSelected()){
            deliveryReport();
        }
        
        if(returnsCheckBox.isSelected()){
            returnsReport();
        }
        
        if(transferCheckBox.isSelected()){
            transferReport();
        }
        
        if(adjustmentsCheckBox.isSelected()){
            adjustmentReport();
        }
        
        if(tradeInCheckBox.isSelected()){
            tradeInReport();
        }
        
        if(replacementCheckBox.isSelected()){
            replacementReport();
        }
    }
    
    public void updateDefectiveReportsTable(){
        try{
            
            ResultSet rs = dbConnector.query("SELECT name, return_quantity, Supplier_name, type_name FROM product JOIN returns USING(product_id) JOIN supplier JOIN type");
            while(rs.next()){
                String product = rs.getString("name");
                String supplier = rs.getString("Supplier_name");
                String category = rs.getString("type_name");
                String quantity = rs.getString("return_quantity");
                
                String[] values = {product, supplier, category, quantity, "defective"};
                inventoryTableManager.addRowContent(values);
            }
            
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
    }
    
    public void updateSecondHandReportsTable(){
        try{
            String selectString = "SELECT used_item_name, " +
                "type_name" +
                "FROM secondhand JOIN type ON type.type_id = used_item_type";
            ResultSet rs = dbConnector.query(selectString);

            while(rs.next()){
                String product = rs.getString("used_item_name");
                String supplier = "N/A";
                String category = rs.getString("type_name");
                String quantity = "1";
                
                String[] values = {product, supplier, category, quantity, "secondhand"};
                inventoryTableManager.addRowContent(values);
            }
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
    }
    
    public void updateBrandNewReportsTable(){
        try{
            
            ResultSet rs = dbConnector.query("SELECT type_name, name, supplier_name, physical_count FROM product JOIN type USING(type_id) JOIN supplier using(supplier_id) ORDER BY 2 ASC"); 
            while(rs.next()){
                String product = rs.getString("name");
                String supplier = rs.getString("supplier_name");
                String category = rs.getString("type_name");
                String quantity = rs.getString("physical_count");
                
                String[] values = {product, supplier, category, quantity, "brand new"};
                inventoryTableManager.addRowContent(values);
            }
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
    }
    
    public void deliveryReport(){        
                
        try{            
            DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
            String toDate = InputValidator.dateFormat(dateFrom);
            String fromDate = InputValidator.dateFormat(dateTo);
            ResultSet rs = dbConnector.query("SELECT delivery_id, date_order_received, name, quantity_delivered, delivery_receipt, Supplier_name, remarks FROM delivery JOIN deliverydetails using(Delivery_id) JOIN product using(Product_id) JOIN supplier ON supplier.supplier_id = delivery.supplier_id WHERE date_order_received between '" + toDate + "' and '" + fromDate + "' ORDER BY 2");
            
            while(rs.next()){
                String date = rs.getString("date_order_received");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString("name");
                String quantity = rs.getString("quantity_delivered");
                String description = rs.getString("delivery_receipt");
                String id = rs.getString("Supplier_name");
                String remarks = rs.getString("remarks");
                
                String[] reportDetails = {dateReport, "Delivery", name, quantity, "Receipt no: " + description, remarks, id};
                transactionTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void salesReport(){
        
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT sales_date, Name, quantity_sold, receipt_number, serial_number, sales_type, remarks FROM sales JOIN sales_details using(sales_id) JOIN product using(Product_id) WHERE sales.sales_id = sales_details.sales_id AND sales_details.product_id = product.Product_id AND sales_date between '" + toDate + "' and '" + fromDate + "'");

            while(rs.next()){
                
                String date = rs.getString("sales_date");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString("Name");
                String quantity = rs.getString("quantity_sold");
                if(quantity.equals("0")){
                    quantity = "Partial";
                }
                String description = rs.getString("receipt_number");
                String serial = rs.getString("serial_number");
                String type = rs.getString("sales_type");
                String remarks = rs.getString("remarks");
                String[] reportDetails = {dateReport, "Sales", name, quantity, "Receipt number:  " + description, remarks, serial, type};
                transactionTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
                
    }
    
    public void replacementReport(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT rep_date, T1.name, T2.name, replacements.remarks, receipt_number, quantity FROM replacements JOIN product T1 USING(product_id) JOIN product T2 ON replacements.replacement_product_id = T2.product_id JOIN sales USING(sales_id) WHERE rep_date between '" + toDate + "' and '" + fromDate + "' ORDER BY 1 DESC");

            while(rs.next()){
                String date = rs.getString("rep_date");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString(2);
                String quantity = rs.getString("quantity");
                String description = rs.getString(3);
                String remarks = rs.getString(4);
                //String value2 = rs.getString("old_item_status");
                String value1 = rs.getString("receipt_number");
                String[] reportDetails = {dateReport, "Replacement", name, quantity, "Replaced with: " + description, remarks, value1};
                transactionTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void returnsReport(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT return_id, return_date, return_quantity, name, supplier_name, remarks, item_status, serial_number FROM returns JOIN product using(Product_id) JOIN supplier ON supplier.supplier_id = returns.supplier_id WHERE return_date between '" + toDate + "' and '" + fromDate + "'");

            while(rs.next()){
                String date = rs.getString("return_date");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString("name");
                String quantity = rs.getString("return_quantity");
                String description = rs.getString("supplier_name");
                String remarks = rs.getString("remarks");
                String value2 = rs.getString("item_status");
                String value1 = rs.getString("serial_number");
                String[] reportDetails = {dateReport, "Returns", name, quantity, "Supplier: " + description, remarks, value1, value2};
                transactionTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }                
    }
    
    public void transferReport(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT Transfer_date, Name, qty_transfered, Transfer_from, Destination, remarks FROM transferdetails JOIN product using(Product_id) JOIN transfers using(Transfer_id) WHERE transferdetails.transfer_id = transfers.transfer_id AND transferdetails.product_id = product.Product_id AND Transfer_date between '" + toDate + "' and '" + fromDate + "'");

            while(rs.next()){
                String date = rs.getString("Transfer_date");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString("Name");
                String quantity = rs.getString("qty_transfered");
                String description = "from " + rs.getString("Transfer_from") + " to " + rs.getString("Destination");
                String remarks = rs.getString("remarks");
                String value1 = rs.getString("Transfer_from");
                String value2 = rs.getString("Destination");
                
                String[] reportDetails = {dateReport, "Transfer", name, quantity, description, remarks, value1, value2};
                transactionTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
                
    }
    
    public void adjustmentReport(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT date_adjusted, Name, quantity_adjusted, reason, operation FROM adjustment JOIN adjustment_details using(adjustment_id) JOIN product using(Product_id) WHERE adjustment.adjustment_id = adjustment_details.adjustment_id AND adjustment_details.product_id = product.Product_id AND date_adjusted between '" + toDate + "' and '" + fromDate + "'");

            while(rs.next()){
                String date = rs.getString(1);
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString(2);
                String quantity = rs.getString(3);
                String description = rs.getString(5);
                String remarks = rs.getString(4);
                
                String[] reportDetails = {dateReport, "Adjustments", name, quantity, description, remarks};
                transactionTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
                
    }
    
    public void tradeInReport(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT swap_id, date_swapped, T2.used_item_name, T1.used_item_name, name  FROM secondhand_swap LEFT JOIN secondhand T1 USING(used_item_id) LEFT JOIN secondhand_swap_details USING(swap_id) LEFT JOIN secondhand T2 ON customer_item_id = T2.used_item_id  LEFT JOIN product USING(product_id) WHERE date_swapped between '" + toDate + "' and '" + fromDate + "'");
            String previousSwapID = "";
            
            while(rs.next()){
                String date = rs.getString("date_swapped");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString(3);
                String secHand = rs.getString(4);
                String prod = rs.getString("name");
                String swapID = rs.getString("swap_id");
                
                //String remarks = rs.getString("remarks");
                String desc = "";
                String value = "";
                if(rs.getString(4) == null){
                    desc = "Swapped to: " + prod;
                    value = "Swapped to Brand New";
                }else{
                    desc = "Swapped to: " + secHand;
                    value = "Swapped to Second Hand";
                }
                if(swapID.equals(previousSwapID)){
                    String cellValue = transactionTableManager.getValueAt(transactionTableManager.getRowCount()-1, 2);
                    String quantity = transactionTableManager.getValueAt(transactionTableManager.getRowCount()-1, 3);
                    
                    transactionTableManager.setValueAt(transactionTableManager.getRowCount()-1, 2, cellValue+", " + name);
                    transactionTableManager.setValueAt(transactionTableManager.getRowCount()-1, 3, Integer.toString((Integer.parseInt(quantity) + 1)));
                }else{
                    String[] reportDetails = {dateReport, "Trade In", name, "1", desc, "", value};
                    transactionTableManager.addRowContent(reportDetails);
                    previousSwapID = swapID;
                }
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
                
    }
    
    public void viewDetails(){
        String selectedTransaction = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 1);
        
        if(selectedTransaction.equals("Sales")){
            viewSalesDetail();
        }else if(selectedTransaction.equals("Delivery")){
            viewDeliveryDetail();
        }else if(selectedTransaction.equals("Transfer")){
            viewTransferDetail();
        }else if(selectedTransaction.equals("Returns")){
            viewReturnsDetail();
        }else if(selectedTransaction.equals("Adjustments")){
            viewAdjustmentsDetail();
        }else if(selectedTransaction.equals("Trade In")){
            viewTradeInDetail();
        }else if(selectedTransaction.equals("Replacement")){
            viewReplacementDetail();
        }
            
    }
    
    public void viewSalesDetail(){
        try{
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String rcpt = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
            String[] rec = rcpt.split(" ", 3);
            String recpt = rec[2];
            String serial = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
            String type = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
            String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
                    
            salesProd.setText(product);
            salesDate.setText(date);
            salesReceipt.setText(recpt);
            salesQty.setText(quantity);
            salesType.setText(type);
            salesSerial.setText(serial);
            salesRemarks.setText(remarks);
            salesDialog.setVisible(true);
        }catch(NullPointerException npe){
            try{
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String receipt = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
            String[] rec = receipt.split(" ", 3);
            String recpt = rec[2];
            String serial = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
            String type = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
           
            
            salesProd.setText(product);
            salesDate.setText(date);
            salesReceipt.setText(recpt);
            salesQty.setText(quantity);
            salesType.setText(type);
            salesSerial.setText(serial);
            salesRemarks.setText("");
            salesDialog.setVisible(true);
            }catch(NullPointerException npexc){
                try{
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String receipt = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
            String[] rec = receipt.split(" ", 3);
            String recpt = rec[2];
            String type = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
            String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
            
            salesProd.setText(product);
            salesDate.setText(date);
            salesReceipt.setText(recpt);
            salesQty.setText(quantity);
            salesType.setText(type);
            salesSerial.setText("N/A");
            salesRemarks.setText(remarks);
            salesDialog.setVisible(true);
            }catch(NullPointerException npexcept){
                String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String receipt = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
            String[] rec = receipt.split(" ", 3);
            String recpt = rec[2];
            String type = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
            
            salesProd.setText(product);
            salesDate.setText(date);
            salesReceipt.setText(recpt);
            salesQty.setText(quantity);
            salesType.setText(type);
            salesSerial.setText("N/A");
            salesRemarks.setText("");
            salesDialog.setVisible(true);
            }
            }
        }
    }
    
    public void viewDeliveryDetail(){
        try{
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String rcpt = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
            String[] rec = rcpt.split(" ", 3);
            String receipt = rec[2];
            String supplier = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
            String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
            
            deliveryDate.setText(date);
            deliverySupp.setText(supplier);
            deliveryReceipt.setText(receipt);
            deliveryQty.setText(quantity);
            deliveryProd.setText(product);
            deliveryRemarks.setText(remarks);
            deliveryDialog.setVisible(true);
        }catch(NullPointerException npe){
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String rcpt = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
            String[] rec = rcpt.split(" ", 3);
            String receipt = rec[2];
            String supplier = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
            //String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
            
            deliveryDate.setText(date);
            deliverySupp.setText(supplier);
            deliveryReceipt.setText(receipt);
            deliveryQty.setText(quantity);
            deliveryProd.setText(product);
            deliveryRemarks.setText("");
            deliveryDialog.setVisible(true);
        }
    }
    
    public void viewTransferDetail(){
        try{
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
            String value2 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
            String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
            
            transferProd.setText(product);
            transferQty.setText(quantity);
            transferFrom.setText(value1);
            transferTo.setText(value2);
            transferDate.setText(date);
            transferRemarks.setText(remarks);
            transferDialog.setVisible(true);
        }catch(NullPointerException npe){
            String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
            String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
            String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
            String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
            String value2 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
            
            
            transferProd.setText(product);
            transferQty.setText(quantity);
            transferFrom.setText(value1);
            transferTo.setText(value2);
            transferDate.setText(date);
            transferRemarks.setText("");
            transferDialog.setVisible(true);
        }
    }
    
    private void viewReturnsDetail(){
        try{
        String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
        String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
        String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
        String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
        String[] supplier = desc.split(" ", 2);
        String supp = supplier[1];
        String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
        String value2 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
        String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
        
        returnsProd.setText(product);
        returnsSupplier.setText(supp);
        returnsDate.setText(date);
        returnsQty.setText(quantity);
        returnsRemarks.setText(remarks);
        returnsStatus.setText(value2);
        returnsSerial.setText(value1);
        returnsDialog.setVisible(true);
        }catch(NullPointerException npe){
            try{
                String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
                String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
                String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
                String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
                String[] supplier = desc.split(" ", 2);
                String supp = supplier[1];
                String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
                String value2 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
        
                returnsProd.setText(product);
                returnsSupplier.setText(supp);
                returnsDate.setText(date);
                returnsQty.setText(quantity);
                returnsRemarks.setText("");
                returnsStatus.setText(value2);
                returnsSerial.setText(value1);
                returnsDialog.setVisible(true);
            }catch(NullPointerException npexc){
                try{
                String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
                String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
                String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
                String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
                String[] supplier = desc.split(" ", 2);
                String supp = supplier[1];
                String value2 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
                String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
                
                returnsProd.setText(product);
                returnsSupplier.setText(supp);
                returnsDate.setText(date);
                returnsQty.setText(quantity);
                returnsRemarks.setText(remarks);
                returnsStatus.setText(value2);
                returnsSerial.setText("");
                returnsDialog.setVisible(true);
            }catch(NullPointerException npexcep){
                String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
                String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
                String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
                String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
                String[] supplier = desc.split(" ", 2);
                String supp = supplier[1];
                String value2 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 7);
                
                returnsProd.setText(product);
                returnsSupplier.setText(supp);
                returnsDate.setText(date);
                returnsQty.setText(quantity);
                returnsRemarks.setText("");
                returnsStatus.setText(value2);
                returnsSerial.setText("");
                returnsDialog.setVisible(true);
            }
            }
        }
    }
    
    private void viewTradeInDetail(){
        try{
        String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
        String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
        String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
        String swapValue = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
        String[] swap = swapValue.split(" ", 3);
        String swappedTo = swap[2];
        String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
        //String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
        
        tradeProd.setText(product);
        tradeTo.setText(swappedTo);
        tradeType.setText(value1);
        tradeQty.setText(quantity);
        tradeDate.setText(date);
        //tradeRemarks.setText(remarks);
        tradeInDialog.setVisible(true);
        
        }catch(NullPointerException npe){
            npe.printStackTrace();
        }
    }
    
    private void viewAdjustmentsDetail(){
        try{
        String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
        String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
        String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
        String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);        
        String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
        
        adjProd.setText(product);
        adjReason.setText(remarks);
        adjOperation.setText(desc);
        adjQty.setText(quantity);
        adjDate.setText(date);
        adjustmentsDialog.setVisible(true);
        }catch(NullPointerException npe){
        String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
        String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
        String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
        String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);        
        String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
        
        adjProd.setText(product);
        adjReason.setText("");
        adjOperation.setText(desc);
        adjQty.setText(quantity);
        adjDate.setText(date);
        adjustmentsDialog.setVisible(true);
        }
    }
    
    private void viewReplacementDetail(){
        try{
        String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
        String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
        String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
        String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
        String[] value = desc.split(" ", 3);
        String description = value[2];
        String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
        String remarks = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 5);
        rplProd.setText(product);
        rplRemarks.setText(remarks);
        rplRcpt.setText(value1);
        rplQty.setText(quantity);
        rplDate.setText(date);
        rplWith.setText(description);
        replacementDialog.setVisible(true);
        }catch(NullPointerException npe){
        String date = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 0);
        String product  = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 2);
        String quantity = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 3);
        String desc = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 4);
        String[] value = desc.split(" ", 3);
        String description = value[2];
        String value1 = transactionTableManager.getValueAt(transactionTableManager.getSelectedRow(), 6);
        
        rplProd.setText(product);
        rplRemarks.setText("");
        rplRcpt.setText(value1);
        rplQty.setText(quantity);
        rplDate.setText(date);
        rplWith.setText(description);
        replacementDialog.setVisible(true);
        }
    }
    
    public void justDClickIt(JTable table){
    table.addMouseListener(new MouseAdapter() {
    public void mousePressed(MouseEvent me) {
        JTable table =(JTable) me.getSource();
        Point p = me.getPoint();
        int row = table.rowAtPoint(p);
        if (me.getClickCount() == 2) {
            viewDetails();
            }
        }
    });
    }
    public void filterProducts(){

         ArrayList<Product> productList = productController.getProducts();
         ArrayList<Integer> coloredRows = new ArrayList<Integer>();
         if(productFilterCategoryComboBox.getSelectedItem()!=null && productList != null && productFilterSupplierComboBox.getSelectedItem()!=null){
            inventoryTableManager.clearTableContents();
            ArrayList<Product> filteredByCategoryList = filterByCategory( productList);
            ArrayList<Product> filteredByCategoryAndSupplierList = filterBySupplier(filteredByCategoryList);
            
            if(clearMark.isVisible()){
                search();
            }else{
                int rowCount = 0;
                for(Product product:filteredByCategoryAndSupplierList){
                    String[] productValues = {product.getName(), 
                        product.getSupplierName(),
                        product.getTypeName(),
                        product.getPhysicalCount()};   
                        rowCount++;
                        inventoryTableManager.addRowContent(productValues);
                        if(Integer.parseInt(product.getPhysicalCount()) <= Integer.parseInt(product.getReorderQuantityLevel())){
                            //coloredRows.add(inventoryTableManager.getRowCount()-1);
                            coloredRows.add(rowCount);
                        }
                }
                //inventoryTableManager.setRowStatusColor(coloredRows, Color.PINK);
                
            }
         }        
    }
     
     public ArrayList<Product> filterByCategory(ArrayList<Product> productList){
         ArrayList<Product> filteredProductList = new ArrayList<Product>();
         if(productFilterCategoryComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(Product product:productList){
                if(productFilterCategoryComboBox.getSelectedItem().toString().equals(product.getTypeName())){
                    filteredProductList.add(product);
                }
            }
            return filteredProductList;
         }
     }
     public ArrayList<Product> filterBySupplier(ArrayList<Product> productList){
         ArrayList<Product> filteredProductList = new ArrayList<Product>();
         if(productFilterSupplierComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(Product product:productList){
                if(productFilterSupplierComboBox.getSelectedItem().toString().equals(product.getSupplierName())){
                    filteredProductList.add(product);
                    
                }
            }
            return filteredProductList;
         }
     }
    
    public void search(){
        String productName = searchTextField.getText();
        ArrayList<Product> productList = productController.getProducts();
        inventoryTableManager.clearTableContents();
        
        ArrayList<Product> filteredByCategoryList = filterByCategory(productList);
        ArrayList<Product> filteredBySupplierAndCategoryList = filterBySupplier(filteredByCategoryList);
        for(Product product: filteredBySupplierAndCategoryList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
            String[] adminCompleteValues = {product.getName(), 
                     product.getSupplierName(),
                     product.getTypeName(),
                     product.getPhysicalCount()};
                inventoryTableManager.addRowContent(adminCompleteValues);
        }
        }
        if(productName.equals("")){
            clearMark.setVisible(false);
        }else{
            clearMark.setVisible(true);
        }
    }
    
    public void clearSearch(){
        searchTextField.setText("");
        search();
        clearMark.setVisible(false);
    }
    
    public void currentInventoryToExcel(JTable table, File file){
		try{
			TableModel model = table.getModel();
			FileWriter excel = new FileWriter(file);

			for(int i = 0; i < model.getColumnCount(); i++){
				excel.write(model.getColumnName(i) + "\t");
			}

			excel.write("\n");

			for(int i=0; i< model.getRowCount(); i++) {
				for(int j=0; j < model.getColumnCount(); j++) {
					excel.write(model.getValueAt(i,j).toString()+"\t");
				}
				excel.write("\n");
			}

			excel.close();
		}catch(IOException e){ System.out.println(e); }
	}
            
        public void saveInventoryReportFile(){        
            JFileChooser fc = new JFileChooser();
                int option = fc.showSaveDialog(null);
                if(option == JFileChooser.APPROVE_OPTION){
                    String filename = fc.getSelectedFile().getName(); 
                    String path = fc.getSelectedFile().getParentFile().getPath();                    

					int len = filename.length();
					String ext = "";
					String file = "";

					if(len > 4){
						ext = filename.substring(len-4, len);
					}

					if(ext.equals(".xls")){
						file = path + "\\" + filename; 
					}else{
						file = path + "\\" + filename + ".xls"; 
					}
					currentInventoryToExcel(inventoryTable, new File(file));
				}
        }
        
        public void transactionToExcel(JTable table, File file){
		try{
			TableModel model = table.getModel();
			FileWriter excel = new FileWriter(file);
                        int columns = model.getColumnCount();
                        int col = columns - 2;
			for(int i = 0; i < col; i++){
				excel.write(model.getColumnName(i) + "\t");
			}

			excel.write("\n");
                        
                        
			for(int i=0; i< model.getRowCount(); i++) {
				for(int j=0; j < col; j++) {
                                    try{                                                                            
                                        excel.write(model.getValueAt(i,j).toString()+"\t");
                                    }catch(NullPointerException npe){
                                        excel.write("");
                                    }
                                }
				excel.write("\n");
			}
                        
			excel.close();
		}catch(IOException e){ 
                    e.printStackTrace();
                }
	}
        
        public void saveTransactionReportFile(){        
            JFileChooser fc = new JFileChooser();
                int option = fc.showSaveDialog(null);
                if(option == JFileChooser.APPROVE_OPTION){
                    String filename = fc.getSelectedFile().getName(); 
                    String path = fc.getSelectedFile().getParentFile().getPath();

					int len = filename.length();
					String ext = "";
					String file = "";

					if(len > 4){
						ext = filename.substring(len-4, len);
					}

					if(ext.equals(".xls")){
						file = path + "\\" + filename; 
					}else{
						file = path + "\\" + filename + ".xls"; 
					}
					transactionToExcel(transactionTable, new File(file));
				}
        }
   }
